<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>sap paper test</title>
 <link href="http://google-code-prettify.googlecode.com/svn/trunk/src/prettify.css" type="text/css" rel="stylesheet" />
 <script src="http://google-code-prettify.googlecode.com/svn/trunk/src/prettify.js" type="text/javascript"></script>
</head>

<body onload="prettyPrint()" >
<h3>昨天去笔了一回sap, 碰到sql直接倒下了，因为没有尝试过sql太多的高级语法,回来后有时间研究了下，记录下来。</h3>
<p>题目大致意思是要用sql完成如下的数据抽取,数据库为mysql</p>
<p >
original data:<br/>
<img src="images/data1.png" />
</p>
<p>
transform 1:<br/>
grade A:[0,60)<br/>
grade B:[60,80)<br/>
grade C:[80,90)<br/>
grade D:[90,100]<br/>
column 'num' 代表各个级别的人数<br/>
<img src="images/sap_data_2.png" /> 
<pre class="prettyprint">
/*
*sql for question 1
* mysql not support select into table synatx
*/
create table
score_level
select class,
(
select case 
when x.score <60 
	then 'D'
when x.score <80
	then 'C'
when x.score <90
	then 'B'
when x.score >=90
	then 'A'
end
) as grade,
count(*) num
from score x
group by class,grade
order by class
;
</pre>	
</p>
<p>
from transform 1 to transform 2:<br/>
grade_1代表grade A<br/>
grade_2代表grade B<br/>
grade_3代表grade C<br/>
grade_4代表grade D<br/>
<img src="images/sap_data_3.png" />
<pre class="prettyprint">
/*answer for question 2*/
select y.class,
       sum(y.grade_1) as grade_1,
	   sum(y.grade_2) as grade_2,
	   sum(y.grade_3) as grade_3,
	   sum(y.grade_4) as grade_4
from 
(
select class , 
	(select case 
	when (strcmp(x.grade,'A') = 0)
		then x.num
	else
		0
	end
	)grade_1,
	(select case 
	when (strcmp(x.grade,'B') = 0)
		then x.num
	else 0
	end
	)grade_2,
	(select case 
	when (strcmp(x.grade,'C') = 0)
		then x.num
	else 0
	end
	)grade_3,
	(select case 
	when (strcmp(x.grade,'D') = 0)
		then x.num
	else 0
	end
	)grade_4
from score_level x
) as y
group by y.class
;
</pre>
</body>
</p>

